<?php
class Model_DbTable_Roster_Staffinfo extends Zend_Db_Table_Abstract {

	protected $_name = 'staff_info_detail';
	
	public function getStaffinfo( $idStaff){
		
		$row = $this->fetchRow("`id_staff` = ". $idStaff);
		if(!$row) return false;
		return $row->toArray();		
		
		}
		
	public function getStaffInfoByName($lastName ,$firstName){
		$row = $this->fetchAll("`last_name` LIKE '". $lastName."' AND `first_name` LIKE '".$firstName."'");
		if(!$row) return false;
		return $row->toArray();
	}	
	
	public function updateManager($idStaff,$managerId){
		$data = array("id_manger_belong"=>$managerId);

		$this->update($data,"`id_staff` = ". $idStaff);		
	}
	
	public function deleteManager($idStaff){
		$data = array("id_manger_belong"=>null);
		
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	
	public function listNullManager(){
		$rows =$this->fetchAll("`id_manger_belong` is null AND `leave` != 9","id_staff DESC ");
		if(!$rows) return false;
		return $rows->toArray();
		
	}
	
	public function listStaffByManager($idManager){
		if($idManager != 0){
			$rows =$this->fetchAll("`id_manger_belong` = ".$idManager." AND `leave` != 9","id_staff DESC ");
		}else{
			$rows =$this->fetchAll("`id_manger_belong` is not null AND `leave` != 9","id_staff DESC ");
		}
		
		if(!$rows) return false;
		return $rows->toArray();
	}
		
	public function addStaffinfo( $idStaff,$firstName,$lastName, $gender,$idCard , $idRecord ,$idRecordCash ,$emailStaff , $mobileStaff, $addr,$suburb,$state,$postcode,$fileName,$activeStaff,$idManager,$isWh = null){
		
		$data = array(
		 "last_name" => $lastName,
		 "first_name" =>$firstName,			
		 "id_staff" => $idStaff,		
         "id_card" =>  $idCard ,
         "id_record" =>  $idRecord ,
		 "id_record_cash" => $idRecordCash,
         "email_staff" =>  $emailStaff,
         "active_staff" =>  $activeStaff,
		 "mobile_staff" => $mobileStaff,
		 "addr_staff" => $addr,
		 "suburb_staff" => $suburb,
		 "state_staff" => $state,
		 "postcode" => $postcode,
		 "gender" => $gender,
		 "register_date" =>Model_DatetimeHelper::dateToday(),
		 "id_manager_introduce" => $idManager,
		 "id_manger_belong" => $idManager,
		 "id_package" => 5,
		 "is_wh" => $isWh									
			);
		$this->insert($data);
		
		}
		public function updateBasicStaffinfo( $idStaff,$firstName,$lastName, $gender,$emailStaff , $mobileStaff, $addr,$suburb,$state,$postcode){
		
			$data = array(
				 "last_name" => $lastName,
				 "first_name" =>$firstName,
				 "email_staff" =>  $emailStaff,
				 "mobile_staff" => $mobileStaff,
				 "addr_staff" => $addr,
				 "suburb_staff" => $suburb,
				 "state_staff" => $state,
				 "postcode" => $postcode,
				 "gender" => $gender
			);
		$this->update($data,"`id_staff` = ". $idStaff);		
		
		}
		
		/**
		 * Update staff email
		 * @author Leo
		 * @param unknown_type $idStaff
		 * @param unknown_type $emailStaff
		 */
		public function updateStaffEmail( $idStaff,$emailStaff){
		
			$data = array(
					"email_staff" =>  $emailStaff
			);
			$this->update($data,"`id_staff` = ". $idStaff);
		
		}
		

		public function updateAddressStaffinfo($idStaff,$addr, $suburb,$state,$postcode){
			$data = array(
					"addr_staff" => $addr,
					"suburb_staff" => $suburb,
					"state_staff" => $state,
					"postcode" => $postcode,
			);
			$this->update($data,"`id_staff` = ". $idStaff);
			
		}
		
		public function updateEmail($idStaff,$email){
			$data = array(
					"email_staff" =>  $email
					);
			$this->update($data,"`id_staff` = ". $idStaff);
		}
		
		public function updateMobile($idStaff, $mobile){
			$data = array(
					 "mobile_staff" => $mobile
			);
			$this->update($data,"`id_staff` = ". $idStaff);			
		}
		
	public function verifyAndUpdateStaffinfo($idStaff,$firstName,$lastName,$idCard,$idRecord,$type){
		
		$data = array(
				"last_name" => $lastName,
				"first_name" =>$firstName,
				"id_card" =>  $idCard ,
				"id_record" =>  $idRecord ,
				"id_record_cash" => 0,
				"id_package" => $type,
				"active_staff" => 1		
		);		
		
		
		if($type == 2 || $type == 5){
		$data = array(
				"last_name" => $lastName,
				"first_name" =>$firstName,
				"id_card" =>  $idCard ,
				"id_record" =>  $idRecord ,
				"id_record_cash" => $idRecord,
				"id_package" => $type,
				"active_staff" => 1				
				);
		}

		$this->update($data,"`id_staff` = ". $idStaff);		
		
	}	
	public function updateStaffinfo(  $idStaff ,  $idCard , $idRecord , $emailStaff , $activeStaff){
		$data = array(
			
         "id_card" =>  $idCard ,
         "id_record" =>  $idRecord ,
         "email_staff" =>  $emailStaff ,
         "active_staff" =>  $activeStaff 
	
			);
			
		$this->update($data,"`id_staff` = ". $idStaff);
		}
		
	public function updateMyobInfor($idStaff,$idCard,$idRecord,$idRecordCash,$idPackage,$newPackageDate,$oldIdPackage,$oldPackageDate){
		
		$data = array(
					
				"id_card" =>  $idCard ,
				"id_record" =>  $idRecord ,
				"id_record_cash" =>  $idRecordCash,
				"id_package" =>  $idPackage,
				'old_id_package' => $oldIdPackage,
				'new_package_date' => $newPackageDate,
				'old_package_date' => $oldPackageDate
		);
			
		$this->update($data,"`id_staff` = ". $idStaff);		
		
		
	}	

	public function updatePersonalData($idStaff,$passport,$driverLicense,$visaType,$visaDate,$tfnNo,$bankBsb,$bankAccNo,$bankName,$cardNo,$medicareNo,$studentNo){
		$data = array(
			"no_passport" => Model_EncryptHelper::sslPassword(trim($passport)),	
			"driver_license" => Model_EncryptHelper::sslPassword(trim($driverLicense)),
			"visa_type" => Model_EncryptHelper::sslPassword(trim($visaType)),
			"visa_date" => Model_EncryptHelper::sslPassword(trim($visaDate)),
			"visa_date2" => $visaDate,
			"no_tfn" => Model_EncryptHelper::sslPassword(trim($tfnNo)),
			"bank_bsb" => Model_EncryptHelper::sslPassword(trim($bankBsb)),
			"bank_acc_no" => Model_EncryptHelper::sslPassword(trim($bankAccNo)),
			"bank_acc_name" => Model_EncryptHelper::sslPassword(trim($bankName)),
			"no_credit" => Model_EncryptHelper::sslPassword(trim($cardNo)),
			"no_medicare" => Model_EncryptHelper::sslPassword(trim($medicareNo)),
			"no_student" => Model_EncryptHelper::sslPassword(trim($studentNo))
				);
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	
	public function updateBankDetails($idStaff,$bankBsb,$bankAccNo,$bankName){
		
		$data = array(
				"bank_bsb" => Model_EncryptHelper::sslPassword(trim($bankBsb)),
				"bank_acc_no" => Model_EncryptHelper::sslPassword(trim($bankAccNo)),
				"bank_acc_name" => Model_EncryptHelper::sslPassword(trim($bankName))
		);
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	
	public function updatePersonalDatawithPosition($idStaff,$passport,$driverLicense,$visaType,$visaDate,$tfnNo,$bankBsb,$bankAccNo,$bankName,$position,$cardNo,$medicareNo,$studentNo){
		$data = array(
				"no_passport" => Model_EncryptHelper::sslPassword(trim($passport)),
				"driver_license" => Model_EncryptHelper::sslPassword(trim($driverLicense)),
				"visa_type" => Model_EncryptHelper::sslPassword(trim($visaType)),
				"visa_date" => Model_EncryptHelper::sslPassword(trim($visaDate)),
				"visa_date2" => $visaDate,
				"no_tfn" => Model_EncryptHelper::sslPassword(trim($tfnNo)),
				"bank_bsb" => Model_EncryptHelper::sslPassword(trim($bankBsb)),
				"bank_acc_no" => Model_EncryptHelper::sslPassword(trim($bankAccNo)),
				"bank_acc_name" => Model_EncryptHelper::sslPassword(trim($bankName)),
				"position_status" => $position,
				"no_credit" => Model_EncryptHelper::sslPassword(trim($cardNo)),
				"no_medicare" => Model_EncryptHelper::sslPassword(trim($medicareNo)),
				"no_student" => Model_EncryptHelper::sslPassword(trim($studentNo))
		);
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	
	

	public function updateStaffType($idStaff,$type){
		
		
		$data = array( "id_package" => $type);
		$this->update($data,"`id_staff` = ". $idStaff);
	
	}	
	
	public function updatePaymentInfo($idStaff,$oldRate,$oldDate,$newRate,$newDate){
		$data = array(
			"old_rate" => $oldRate,
			"old_rate_date" => $oldDate,
			"new_rate" => $newRate,
			"new_rate_date" => $newDate			
			);
		$this->update($data,"`id_staff` = ". $idStaff);	
	}
	
	public function updateRateOnceOff($idStaff,$newRate){
		$data = array(
				"new_rate" => $newRate,
		);
		$this->update($data,"`id_staff` = ". $idStaff);		
		
	}
	
	public function updateLeave($idStaff,$isLeave,$dateLeave){
		$data = array(
			"leave" => $isLeave,
			"leave_date" => $dateLeave		
				);
		$this->update($data,"`id_staff` = ". $idStaff);		
	}
	
	public function updatePaperDoc($idStaff,$isComplete){
		
		$data = array("paper_doc_ready" => $isComplete);
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	
	public function updatePosition($idStaff,$positionStatus){
	
		$data = array("position_status" => $positionStatus);
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	
	public function deleteStaffinfo( $idStaff){
		
		$this->delete("`id_staff` = ". $idStaff);
		
		}	
	
	public function listAll(){
		
		$rows =$this->fetchAll("1","id_staff DESC ");
		if(!$rows) return false;
		return $rows->toArray();			
		
		}	
		
	public function checkEmailExist($email){
		
		$row = $this->fetchRow("`email_staff` LIKE '". $email ."'");
		if(!$row) return false;
		return true;
		
		
	}
	
	
	
	public function disActiveStaff($idStaff){
		$data = array(
				"active_staff" => 0,
				"leave"=> 9
		);
		$this->update($data,"`id_staff` = ". $idStaff);		
	}
	public function activeStaff($idStaff){
		$data = array(
				"active_staff" => 1,
				"leave" => 1,
				"leave_date" => null
		);
		$this->update($data,"`id_staff` = ". $idStaff);		
		
	}
	
	public function updateRegDate($idStaff,$regdate){
		$data = array(
				"register_date" =>$regdate,
				"id_manager_introduce" => 110
				);
		$this->update($data,"`id_staff` = ". $idStaff);				
	}
	
	public function getstaffIDByRecordID($recordID,$type){

		$whereStr = "`id_record` = {$recordID} AND (`id_package` = 1 || `id_package` = 3 || `id_package` = 4  ||`id_package` = 6  || `id_package` = 7 || `id_package` = 8 )";
		if($type == "cash"){
			$whereStr = "`id_record` = {$recordID} AND (`id_package` = 2 || `id_package` = 5 )";
		}
		
		$row = $this->fetchRow($whereStr);
		if(!$row) return false;
		return $row;
	}
	
	public function listStaffByType($type){
		$whereStr = "`id_package` = 1 || `id_package` = 3 || `id_package` = 4  ||`id_package` = 6  || `id_package` = 7 || `id_package` = 8 ";
		
		if($type == "cash"){
			$whereStr = " `id_package` = 2 || `id_package` = 5 ";
		}
		
		$rows = $this->fetchAll($whereStr,"last_name");
		
		
		
		
	}
	
	public function updateEmailDetailsOnceOff($idStaff,$emailStaff,$mobileStaff,$addr,$suburb,$state,$postcode){
		
		$data = array(
				
				"email_staff" =>  $emailStaff,
				"mobile_staff" => $mobileStaff,
				"addr_staff" => $addr,
				"suburb_staff" => $suburb,
				"state_staff" => $state,
				"postcode" => $postcode,
				
		);
		$this->update($data,"`id_staff` = ". $idStaff);
		
	}
	/**
	 * This is use in the ETL listing Function , for search people by first and last name 
	 * @param string $firstName
	 * @param string $lastName
	 * @return boolean|Ambigous <Zend_Db_Table_Row_Abstract, NULL, unknown>
	 */
	public function searchStaffName($firstName,$lastName){
		
		$whereStr = "`last_name` LIKE '{$lastName}' AND `first_name` LIKE '{$firstName}' ";

		$row = $this->fetchRow($whereStr);
		if(!$row) return false;
		return $row->toArray();		
	
	}
	/**
	 * This is use for update the Exp Rate in ETL listing function 
	 * @author Norman
	 * @version 2014-06-20
	 * @param int $idStaff
	 * @param float $rate
	 */
	
	public function upDateExpRate($idStaff,$rate){
		
		$data = array(
				"limit_rate" =>$rate,
				"limit_rate_date" => Model_DatetimeHelper::dateToday()
		);
		$this->update($data,"`id_staff` = ". $idStaff);		
		
	}
	
	/**
	 * For update 3 types check status
	 * @version 2014-09-03
	 * @author Leo 
	 * @param unknown_type $idStaff
	 * @param unknown_type $changes
	 */
	public function updateCheckStatus($idStaff,$changes){
	
		if($changes == "type"){
			$data = array(
				"new_package_check" => null,
			);
		}
		if($changes == "rate"){
			$data = array(
				"new_rate_check" => null,
			);
		}
		if($changes == "leave"){
			$data = array(
				"leave_check" => null,
			);
		}
		$this->update($data,"`id_staff` = ". $idStaff);
	
	}
	/**
	 * This is for change summary , list 3 type of changes of every staff in given time 
	 * generally 2 weeks
	 * @author Norman
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 * @param unknown_type $changes
	 */
	
	public function listChanges($dateBegin,$dateEnd,$changes){
		
		
		$whereStr = "";
		
		if($changes == "type"){
			
			$whereStr = "`new_package_date` >= '{$dateBegin}' AND `new_package_date` <= '{$dateEnd}'";
		}
		
		if($changes == "rate"){
			
			$whereStr = "`new_rate_date` >= '{$dateBegin}' AND `new_rate_date` <= '{$dateEnd}'";
		}
		if($changes == "leave"){
			
			$whereStr ="`leave_date` >= '{$dateBegin}' AND `leave_date` <= '{$dateEnd}'";
		}
		
		$rows =$this->fetchAll($whereStr,"id_staff DESC ");
		return $rows->toArray();
	}
	/**
	 * This is for change summary , list 3 type of changes of every staff in given time and not hanled
	 * @version 2014-08-12	
	 * @author Leo
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 * @param unknown_type $changes
	 */
	public function listChangesUnhandle($dateBegin,$dateEnd,$changes){
	
	
		$whereStr = "";
	
		if($changes == "type"){
				
			$whereStr = "`new_package_date` >= '{$dateBegin}' AND `new_package_date` <= '{$dateEnd}' AND `new_package_check` is null AND `is_wh` is null";
		}
	
		if($changes == "rate"){
				
			$whereStr = "`new_rate_date` >= '{$dateBegin}' AND `new_rate_date` <= '{$dateEnd}' AND `new_rate_check` is null AND `is_wh` is null";
		}
		if($changes == "leave"){
				
			$whereStr ="`leave_date` >= '{$dateBegin}' AND `leave_date` <= '{$dateEnd}' AND `leave_check` is null AND `is_wh` is null";
		}
	
		$rows =$this->fetchAll($whereStr,"id_staff DESC ");
		return $rows->toArray();
	}
	
	
	/**
	 * This is for change summary , list 3 type of changes of wH staff in given time and not hanled
	 * @version 2014-08-12
	 * @author Leo
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 * @param unknown_type $changes
	 */
	public function listChangesWhUnhandle($dateBegin,$dateEnd,$changes){
	
	
		$whereStr = "";
	
		if($changes == "type"){
	
			$whereStr = "`new_package_date` >= '{$dateBegin}' AND `new_package_date` <= '{$dateEnd}' AND `new_package_check` is null AND `is_wh` = 1";
		}
	
		if($changes == "rate"){
	
			$whereStr = "`new_rate_date` >= '{$dateBegin}' AND `new_rate_date` <= '{$dateEnd}' AND `new_rate_check` is null AND `is_wh` = 1";
		}
		if($changes == "leave"){
	
			$whereStr ="`leave_date` >= '{$dateBegin}' AND `leave_date` <= '{$dateEnd}' AND `leave_check` is null AND `is_wh` = 1";
		}
	
		$rows =$this->fetchAll($whereStr,"id_staff DESC ");
		return $rows->toArray();
	}
	
	
	/**
	 * This is for update 3 type of changes of every staff has checked
	 *
	 * @version 2014-08-12
	 * @author Leo
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 * @param unknown_type $changes
	 */
	public function updateChanges($idStaff,$changes){
		
		if($changes == "type"){
			$data = array(
				"new_package_check" => 1,
			);
		}
		if($changes == "rate"){
			$data = array(
				"new_rate_check" => 1,
			);
		}
		if($changes == "leave"){
			$data = array(
				"leave_check" => 1,
			);
		}
		
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	/**
	 * @version 2015-02-03
	 * @auther Norman
	 * All Action Staff, if trainee, sign to Simon
	 * Simon can see all the trainee regardsless it is active or Not 
	 */
	public function listAllTrainee(){
		
		$whereStr = "`position_status` = '1' AND (`leave` != 9 OR `leave` IS NULL)";
		$rows = $this->fetchAll($whereStr,"last_name");
		
		if(!(boolean)$rows->toArray()) {return false;}
		
		return $rows->toArray();
			
	}
	
	public function updateSuperInfo($idSupercompany,$idPayMethod,$superAccName,$superAccNo,$superAccBsb,$superBillerCode,$superReference,$employeeNo,$payFrequency,$idStaff,$employerAccNo){
		$data = array(
			"super_id_company" => $idSupercompany,
			"super_payment_method" => $idPayMethod,
			"super_acc_name" => $superAccName,
			"super_acc_no" => $superAccNo,
			"super_acc_bsb" => $superAccBsb,
			"super_bpay_biller_code" => $superBillerCode,
			"super_bpay_reference" => $superReference,
			"super_pay_frequency" =>$payFrequency,	
			"super_bulk_employee_id" => $employeeNo,	
			"super_bulk_employer_acc_no" => $employerAccNo						 	
			);
		
		$this->update($data,"`id_staff` = ". $idStaff);
		
	}
	/**
	 * Add by Norman, get and update the status of the staff 
	 * check and update level 1,2,3 doc 
	 * NULL is not filled , -1 uploaded not check , 1 check and pass ,2 check not pass
	 * @param unknown_type $idStaff
	 */
	public function getStaffDocStatus($idStaff){
		
		$row = self::getStaffinfo($idStaff);
		return array($row['scan_doc_l1'],$row['scan_doc_l2'],$row['scan_doc_l3']);
		
	}
	public function updateStatus($idStaff,$lv1,$lv2,$lv3){
		
		$data = array(
			"scan_doc_l1" => $lv1,
			"scan_doc_l2" => $lv2,
			"scan_doc_l3" => $lv3,
				);
		$this->update($data,"`id_staff` = ". $idStaff);
	}
	
	public function checkNeedVerify(){
		$whereStr = "`scan_doc_l1` = -1 OR `scan_doc_l2` = -1 OR `scan_doc_l3` = -1";
		$rows = $this->fetchAll($whereStr,"id_staff ASC");
		if(empty($rows)) return false;
		return $rows->toArray();
	}
	
	public function checkNeedVerifyStaff($arrStaff){
		$arrRes = array();
		foreach($arrStaff as $k => $v){
			
			$arrRes[] = $v['id_staff'];
		}
		
		return array_unique($arrRes,SORT_NUMERIC);
	}
	
}

?>